import os
import sys
import pandas as pd
pd.set_option('display.unicode.east_asian_width', True)
import json
from datetime import datetime
import openpyxl
from openpyxl.styles import Font, Color,PatternFill,Alignment
from openpyxl.utils import get_column_letter
import platform
if platform.system().lower()=='windows':
    import win32com.client
elif platform.system().lower()=='darwin' or platform.system().lower()=='linux':
    # import xlwings as xw
    import msoffcrypto
    import io
import warnings
# 忽略 UserWarning 类型的警告
warnings.filterwarnings("ignore", category=UserWarning, module='openpyxl')

class orders:
    def __init__(self,ktt_config='D:\\工作目录\\ktt\\order_col_map.config'):
        with open(ktt_config,'r',encoding='utf-8') as f:
            self.map_dic=json.load(f)

        with open(os.path.join(os.path.dirname(__file__),'configs','ktt.config'),'r',encoding='utf-8') as f:
            self.ktt_config=json.load(f)
        


    def read_spec(self,spec_xlsx='e:\\temp\\ejj\\三恒商品规格编码.xlsx'):
        df=pd.read_excel(spec_xlsx)
        return df

    def read_orders(self,xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',sheet_name='商品列表'):
        if platform.system().lower()=='windows':
            #去除快团团的密码，原来设置为7788.去除后才能读取
            xlsApp=win32com.client.DispatchEx('Excel.Application')
            xlsApp.EnableEvents=False
            xlsApp.DisplayAlerts=False
            xlwb=xlsApp.Workbooks.Open(xlsx,UpdateLinks=False, ReadOnly=False, Format=None, Password=7788, WriteResPassword='')
            xlwb.SaveAs(xlsx,None,"","")
            xlsApp.Quit()
        elif platform.system().lower()=='darwin' or platform.system().lower()=='linux':
            # 打开受密码保护的文件
            try:
                with open(xlsx, 'rb') as f:
                    office_file = msoffcrypto.OfficeFile(f)
                    office_file.load_key(password='7788')
                    
                    # 解密文件并保存
                    decrypted = io.BytesIO()
                    office_file.decrypt(decrypted)
                    
                    with open(xlsx,'wb') as out_f:
                        out_f.write(decrypted.getvalue())
            except Exception as e:
                if 'Unencrypted document' in str(e):
                    print("文件已解密")
                else:
                    print(e)

        df=pd.read_excel(xlsx,sheet_name=sheet_name,engine='openpyxl')
        return df
    
    def deal_orders(self,xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',sheet_name='商品列表',spec_xlsx='e:\\temp\\ejj\\三恒商品规格编码.xlsx'):
        specs=self.read_spec(spec_xlsx=spec_xlsx)       
        df=self.read_orders(xlsx=xlsx,sheet_name=sheet_name)

        if df['商品编码'].isnull().any():
            raise MyCustomError("商品编码有空，请先处理。")
        
        merged_df = pd.merge(df, specs[['商品编码', '模板名称','三级分类','发货点','打印规格名称']], on='商品编码', how='left')
        df['模板'] = merged_df['模板名称']
        # df['模板'].fillna('默认*',inplace=True)
        df['模板']=df['模板'].fillna('默认*')
        df['三级分类'] = merged_df['三级分类']
        df['发货点'] = merged_df['发货点']
        df['打印规格名称'] = merged_df['打印规格名称']
        
        return df

    def group_orders(self,xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',sheet_name='商品列表',spec_xlsx='e:\\temp\\ejj\\三恒商品规格编码.xlsx'):
        df=self.deal_orders(xlsx=xlsx,sheet_name=sheet_name,spec_xlsx=spec_xlsx)        

        try:
            df.to_clipboard()
        except Exception as e:
            print(e)
            pass
        
        #非默认按模板类型分组
        grp=df.groupby('发货点')

        # 将每个组转换为独立的 DataFrame 并存储在字典中
        df_grps = {name: group.reset_index(drop=True) for name, group in grp}
        
        
        return df_grps

    def exp_orders(self,template,df):      
        try:
            col_map=self.map_dic[template]
        except Exception as e:
            # print(e)
            col_map=self.map_dic['默认']
        new_df=pd.DataFrame(columns=col_map['col_name'])

        fix_text=col_map['fix_col']
        # print('fix text:',fix_text)
        
        #因为是空的，所以先处理有值的
        for new_col, old_col in col_map['map'].items():
            # print(new_col,old_col)
            try:
                if old_col and old_col!='sender_name' and old_col!='sender_tel': # 只有在映射表中有对应值时才进行赋值               
                    new_df[new_col] = df[old_col]
                if old_col=='收货人':
                    recv_col=new_col
                
                if fix_text:
                    for fix_col, fix_name in fix_text.items():                        
                        if new_col==fix_col:
                            # print(old_col,fix_col,fix_name)
                            new_df[new_col]=fix_name
                
            except:
                # if old_col=='寄付月结' or old_col=='电商标快':col
                    # new_df[new_col]=old_col
                pass
        

        
        #再写入统一的发货人姓名和电话
        for new_col, old_col in col_map['map'].items():
            if old_col == 'sender_name':
                if col_map['sender_name']=='default':
                    new_df[new_col] = self.ktt_config['发货人昵称']
                # new_df[new_col] = self.ktt_config['发货人姓名']
                else:
                    new_df[new_col] = col_map['sender_name']
            elif old_col == 'sender_tel':  
                if col_map['sender_tel']=='default':
                    new_df[new_col] = self.ktt_config['发货人电话']
                else:
                    new_df[new_col] = col_map['sender_tel']

        # print('new df:',new_df)

        duplicated_rows = []
        try:
            for _, row in new_df.iterrows():
                for i in range(row["数量"]):
                    new_row = row.copy()
                    new_row["数量"] = 1  # 设置数量为1
                    if i != 0:
                        new_row["订单金额"] = 0  # 第一行保留原来的金额，其他行改为0
                    duplicated_rows.append(new_row)
        except:
            for _, row in new_df.iterrows():
                for i in range(row["托寄物数量"]):
                    new_row = row.copy()
                    new_row["托寄物数量"] = 1  # 设置数量为1
                    if i != 0:
                        new_row["订单金额"] = 0  # 第一行保留原来的金额，其他行改为0
                    duplicated_rows.append(new_row)

        # 创建新的 DataFrame
        try:
            result_df = pd.DataFrame(duplicated_rows).reset_index(drop=True)
            result_df.drop(columns=['订单金额'], inplace=True)
        except:
            pass

        if(col_map['show_qty']=='no'):
            try:
                result_df.drop(columns=['数量'], inplace=True)
            except:
                pass

        gp_by=list(col_map['map'].keys())[col_map['sum_col_num']]
        count_df = result_df.groupby(gp_by).size().reset_index(name="数量")
        total_count=result_df.shape[0]

        stat_txt = "\n".join([f"{row[gp_by]} {row['数量']}件" for index, row in count_df.iterrows()])
        if platform.system().lower()=='windows':
            fn_post = " & ".join([f"{row[gp_by]} {row['数量']}件" for index, row in count_df.iterrows()])
        elif platform.system().lower()=='darwin' or platform.system().lower()=='linux':
            fn_post = " | ".join([f"{row[gp_by]} {row['数量']}件" for index, row in count_df.iterrows()])
        
        fn_post=f'共{total_count}件 {fn_post}'
        
        return {'df':result_df,'total_txt':stat_txt,'fn_post':fn_post,'desc_col':col_map['sum_col_num'],'desc_row':total_count,'recv_col':recv_col,'desc_yn':col_map['summary_total_show_yn']}


    def batch_exp(self,xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',sheet_name='商品列表',spec_xlsx='e:\\temp\\ejj\\三恒商品规格编码.xlsx'):
        df_grp=self.group_orders(xlsx=xlsx,sheet_name=sheet_name,spec_xlsx=spec_xlsx)     

        res={key:self.exp_orders(key,df) for key,df in df_grp.items()}
        
        return res
    
    def batch_exp_save(self,date_input='20240725',
                        batch_num='01',
                        xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',
                        sheet_name='商品列表',
                        spec_xlsx='e:\\temp\\ejj\\三恒商品规格编码.xlsx',
                        save_dir='E:\\temp\\ejj\\团购群\\订单\\给果园的订单',
                        open_dir='yes'):
        save_dir=os.path.join(save_dir,f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}')
        if not os.path.exists(save_dir):
            os.makedirs(save_dir)

        #先删除同样批次的
        for fn in os.listdir(save_dir):
            if fn.split('-')[1]==batch_num and fn.split('.')[-1]=='xlsx':
                os.remove(os.path.join(save_dir,fn))

        dfs=self.batch_exp(xlsx=xlsx,sheet_name=sheet_name,spec_xlsx=spec_xlsx)
        save_names=[]
        save_text=[]
        for key,data in dfs.items():
            print(f'正在处理 {key}...',end='')
            save_name=os.path.join(save_dir,f'{date_input}-{batch_num}-{key}-{data["fn_post"]}.xlsx')
            #三恒发货的，按收件人姓名列排序
            if key=='三恒':
                save_df=data['df'].sort_values(by=data['recv_col'])
            else:
                save_df=data['df']

            save_df.to_excel(save_name,index=False)
            self.xlsx_format(xlsx=save_name,desc=f"{key} 共{data['desc_row']}件：\n{data['total_txt']}",desc_row=data['desc_row']+5,desc_col=data['desc_col']+1,desc_yn=data['desc_yn'])
            save_names.append(save_name)
            save_text.append(f"{date_input} 第{batch_num}批 {key} 共{data['desc_row']}件：\n{data['total_txt']}")
            print('完成')
        if open_dir=='yes':
            if platform.system().lower()=='windows':
                os.startfile(save_dir)
            elif platform.system().lower()=='darwin' or platform.system().lower()=='linux':
                os.system(f'open {save_dir}')
        # print('\n------------------------\n')
        # print('\n\n'.join(save_text))
        return {'file_path':save_names,'sum_txt':'\n\n'.join(save_text)}

    def xlsx_format(self,xlsx,desc,desc_row,desc_col,desc_yn='yes'):
        wb=openpyxl.load_workbook(xlsx)
        ws=wb.active

        # for cell in ws[1]:
            
        #     font=Font(size=13,bold=True)
        #     cell.font=font

        font=Font(size=13,bold=True)
        ws['A1'].font=font

        #写入汇总信息
        if desc_yn=='yes':
            ws.cell(desc_row,desc_col).value=desc
            font=Font(bold=True)
            ws.cell(desc_row,desc_col).font=font
            yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            ws.cell(desc_row,desc_col).fill=yellow_fill
            ws.cell(desc_row,desc_col).alignment = Alignment(wrapText=True)

        for cls in ['A','B','C','D','F','G','H','I']:
            cell=ws[cls+'1']
            font=Font(bold=True)
            cell.font=font


        
        #调整列宽
        col_letter = get_column_letter(desc_col)  # 返回 'B'
        ws.column_dimensions[col_letter].width = 40
        # for cell in ws[1]:
        #     max_length = 0
        #     column_letter = get_column_letter(cell.column)
        #     # print(column_letter,cell.value)
        #     try:
        #         if len(str(cell.value)) > max_length:
        #             max_length = len(cell.value)
        #     except:
        #         pass

        #     if column_letter=='C':
        #         adjusted_width = (max_length + 15) * 3.2
        #     else:
        #         adjusted_width = (max_length + 5) * 3.2
        #     ws.column_dimensions[column_letter].width = adjusted_width
        
        wb.save(xlsx)
        # print('修改格式完成')


class MyCustomError(Exception):
    """自定义异常类"""
    def __init__(self, message):
        super().__init__(message)
        self.message = message

if __name__=='__main__':
    p=orders(ktt_config='D:\\工作目录\\ktt\\order_col_map.config')

    save_names=p.batch_exp_save(date_input='20250102',
                        batch_num='01',
                        xlsx='E:\\temp\\ejj\\团购群\\订单\\20250102-综合-导出订单-01.xlsx',
                        sheet_name='商品列表',
                        spec_xlsx='D:\\工作目录\\ktt\\三恒商品规格编码.xlsx',
                        save_dir='E:\\temp\\ejj\\团购群\\订单\\给果园的订单')
    
    # p=orders(ktt_config='/home/jack/Nutstore Files/ktt/order_col_map.config')

    # save_names=p.batch_exp_save(date_input='20241226',
    #                     batch_num='01',
    #                     xlsx='/media/jack/data/temp/ejj/团购群/订单/20241226-综合-导出订单-01.xlsx',
    #                     sheet_name='商品列表',
    #                     spec_xlsx='D:\\工作目录\\ktt\\三恒商品规格编码.xlsx',
    #                     save_dir='E:\\temp\\ejj\\团购群\\订单\\给果园的订单')
    
    p=orders(ktt_config='/home/jack/Nutstore Files/ktt/order_col_map.config')

    save_names=p.batch_exp_save(date_input='20250109',
                        batch_num='01',
                        xlsx='/media/jack/data/temp/ejj/团购群/订单/20250109-综合-导出订单-01.xlsx',
                        sheet_name='商品列表',
                        spec_xlsx='/home/jack/Nutstore Files/ktt/三恒商品规格编码.xlsx',
                        save_dir='/media/jack/data/temp/ejj/团购群/订单/给果园的订单')

    print(save_names)

